--Function of adding new datetime column
create or replace procedure meta_add_datetime_column(
    --Table id
    a_table_id raw,
    --Column id
    a_column_id raw,
    --Column name
    a_column_name varchar2)
as
    --Table name
    a_table_name varchar2(26);
begin
    p_check_col_not_exists(a_table_id, a_column_id, a_column_name);

    a_table_name :=  p_get_table_name(a_table_id);
    
    --Changing the metadata
    insert into meta_datetime_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (a_column_id, a_table_id, a_column_name,
        p_generate_order_index(a_table_id), 1);
        
    --Dropping table view
    p_drop_view_if_exists(a_table_name);
         
    execute immediate 'alter table tbl_' 
    || a_table_name
    || ' add ' || a_column_name
    || ' date';
         
    --Creating table view
    p_create_table_view(a_table_id);

end;

--go

--Function of setting datetime column minimum
create or replace procedure meta_set_datetime_minimum(
    --Column id
    a_column_id raw,
    --Minimum
    a_minimum date)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Old minimum
    a_old_minimum date;
    --Minimum str
    a_minimum_str varchar2(80);
    --Customization level
    a_customization_level number(10);
begin
    p_check_col_type(a_column_id, 'meta_datetime_columns', 'datetime');
    
    --Reading column properties
    select table_id, minimum, column_name, customization_level
    into a_table_id, a_old_minimum, a_column_name, a_customization_level
    from meta_datetime_columns
    where column_id = a_column_id;
    
    --If this is a system column
    if a_customization_level = 0 then
        raise_application_error(-20008,
                'Changing datetime system column minimum is forbidden (column_id = '
                || a_column_id
                || ') (#error_code = 20008)');
    end if;
    
    a_table_name := p_get_table_name(a_table_id);
    
    --If there is no need for change
    if (a_minimum = a_old_minimum) or
        (a_minimum is null and a_old_minimum is null) then
        return;
    end if;
         
    --There is no need to drop table view
    
    if a_old_minimum is not null then
        --Drop old constraint
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' drop constraint n' || substr(a_column_id, 1, 29);
    end if;         
    
    if a_minimum is not null then
        a_minimum_str := to_char(a_minimum, 'DD-MM-YYYY HH24:MI:SS');
        
        --Create new check
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' add constraint n' ||  substr(a_column_id, 1, 29)
        || ' check(' || a_column_name
        || ' >= to_date(''' || a_minimum_str || ''', ''DD-MM-YYYY HH24:MI:SS''))';
    end if;
    
    --Updating the metadata
    update meta_datetime_columns
    set minimum = a_minimum
    where column_id = a_column_id;
end;

--go

--Function of setting datetime column maximum
create or replace procedure meta_set_datetime_maximum(
    --Column id
    a_column_id raw,
    --Minimum
    a_maximum date)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Old maximum
    a_old_maximum date;
    --Maximum str
    a_maximum_str varchar2(80);
    --Customization level
    a_customization_level number(10);
begin
    p_check_col_type(a_column_id, 'meta_datetime_columns', 'datetime');
    
    --Reading column properties
    select table_id, maximum, column_name, customization_level
    into a_table_id, a_old_maximum, a_column_name, a_customization_level
    from meta_datetime_columns
    where column_id = a_column_id;
    
    --If this is a system column
    if a_customization_level = 0 then
        raise_application_error(-20008,
                'Changing datetime system column maximum is forbidden (column_id = '
                || a_column_id
                || ') (#error_code = 20008)');
    end if;
    
    a_table_name := p_get_table_name(a_table_id);
    
    --If there is no need for change
    if (a_maximum = a_old_maximum) or
        (a_maximum is null and a_old_maximum is null) then
        return;
    end if;
         
    --There is no need to drop table view
    
    if a_old_maximum is not null then
        --Drop old constraint
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' drop constraint x' || substr(a_column_id, 1, 29);
    end if;         
    
    if a_maximum is not null then
        a_maximum_str := to_char(a_maximum, 'DD-MM-YYYY HH24:MI:SS');
        
        --Create new check
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' add constraint x' ||  substr(a_column_id, 1, 29)
        || ' check(' || a_column_name
        || ' <= to_date(''' || a_maximum_str || ''', ''DD-MM-YYYY HH24:MI:SS''))';
    end if;
    
    --Updating the metadata
    update meta_datetime_columns
    set maximum = a_maximum
    where column_id = a_column_id;
end;

--go

--Function of setting column default value
create or replace procedure meta_set_datetime_default(
    --Column id
    a_column_id raw,
    --Default value
    a_default_value date)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Default value str
    a_default_value_str varchar2(80);
    --Customization level
    a_customization_level number(10);
begin
    p_check_col_type(a_column_id, 'meta_datetime_columns', 'datetime');
    
    --Reading column properties
    select table_id, column_name, customization_level
    into a_table_id, a_column_name, a_customization_level
    from meta_datetime_columns
    where column_id = a_column_id;
    
    --If this is a system column
    if a_customization_level = 0 then
        raise_application_error(-20008,
                'Changing datetime system column default value is forbidden (column_id = '
                || a_column_id
                || ') (#error_code = 20008)');
    end if;
    
    a_table_name := p_get_table_name(a_table_id);
    
     if a_default_value is not null then
        a_default_value_str := to_char(a_default_value, 'DD-MM-YYYY HH24:MI:SS');
                
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default to_date(''' || a_default_value_str || ''', ''DD-MM-YYYY HH24:MI:SS''))';
    else
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default null)';
    end if;
    
    --Updating the metadata
    update meta_datetime_columns
    set default_value = a_default_value
    where column_id = a_column_id;
end;

--go